using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Ent.SqlDB
{
    public struct VSqlDbType
    {
        public SqlDbType DBType;
        public int Size;
        public VSqlDbType(SqlDbType dbType)
        {
            DBType = dbType;
            Size = -1;
        }
        public VSqlDbType(SqlDbType dbType, int size)
        {
            DBType = dbType;
            Size = size;
        }
    }
    public class Execute
    {
        static SqlCommand com;
        static SqlConnection con;
        public static int NonQuery(string query)
        {
            con = Connection.Get();
            int retValue = 0;
            com = new SqlCommand(query, con);
            retValue = com.ExecuteNonQuery();
            return retValue;
        }
        public static NonQueryResponse SPNonQuery(string name, Dictionary<string, object> inParams, Dictionary<string, VSqlDbType> outParams)
        {
            NonQueryResponse retValue = new NonQueryResponse();
            SetCommand(name, inParams, outParams);
            retValue.Value = com.ExecuteNonQuery();
            retValue.OutParams = GetOutParams(outParams);
            return retValue;
        }
        public static object Scalar(string query)
        {
            con = Connection.Get();
            object retValue = null;
            com = new SqlCommand(query, con);
            retValue = com.ExecuteScalar();
            return retValue;
        }
        public static ScalarResponse SPScalar(string name, Dictionary<string, object> inParams, Dictionary<string, VSqlDbType> outParams)
        {
            ScalarResponse retValue = new ScalarResponse();
            SetCommand(name, inParams, outParams);
            retValue.Value = com.ExecuteScalar();
            retValue.OutParams = GetOutParams(outParams);
            return retValue;
        }
        public static DataTable Reader(string query)
        {
            con = Connection.Get();
            DataTable retValue = new DataTable();
            com = new SqlCommand(query, con);
            SqlDataReader reader = com.ExecuteReader();
            retValue.Load(reader);
            reader.Close();
            return retValue;
        }
        public static ReaderResponse SPReader(string name, 
            Dictionary<string, object> inParams, 
            Dictionary<string, VSqlDbType> outParams)
        {
            try
            {
                ReaderResponse retValue = new ReaderResponse();
                DataTable dt = new DataTable();
                SetCommand(name, inParams, outParams);
                SqlDataReader reader = com.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                retValue.Table = dt;
                retValue.OutParams = GetOutParams(outParams);
                return retValue;
            }
            catch (Exception exc)
            {
                throw new Exception("Error in " + name + ". " + exc.Message);
            }
        }
        private static Dictionary<string, object> GetOutParams(Dictionary<string, VSqlDbType> outParams)
        {
            Dictionary<string, object> retParams = null;
            if (outParams != null)
            {
                retParams = new Dictionary<string, object>();
                foreach (string key in outParams.Keys)
                {
                    retParams.Add(key, com.Parameters['@' + key].Value);
                }
            }
            return retParams;
        }
        private static void SetCommand(string name, Dictionary<string, object> inParams, Dictionary<string, VSqlDbType> outParams)
        {
            com = new SqlCommand();
            com.Connection = Connection.Get();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = name;
            if (inParams != null)
            {
                foreach (string param in inParams.Keys)
                {
                    SqlParameter sParam = new SqlParameter('@' + param, inParams[param]);
                    sParam.Direction = ParameterDirection.Input;
                    com.Parameters.Add(sParam);
                }
            }
            if (outParams != null)
            {
                foreach (string param in outParams.Keys)
                {
                    VSqlDbType vdb = outParams[param];
                    SqlParameter sParam;
                    if(vdb.Size!=-1)
                        sParam = new SqlParameter('@' + param, vdb.DBType, vdb.Size);
                    else
                        sParam = new SqlParameter('@' + param, vdb.DBType);
                    sParam.Direction = ParameterDirection.Output;
                    com.Parameters.Add(sParam);
                }
            }
        }
    }
}
